﻿using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Data.Entity;
using System.Data.Entity.Core.Metadata.Edm;
using System.Data.SqlClient;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Runtime.Remoting.Contexts;
using System.Text;
using System.Threading.Tasks;
using Xugu.Data.EntityFramework;
using XuguClient;

namespace ef_demo
{
    public class User
    {
        public int ID { get; set; }
        public string NAME { get; set; }
        public int AGE { get; set; }
    }

    // 定义数据库上下文
    [DbConfigurationType(typeof(XGEFConfiguration))]
    public class MyDbContext : DbContext
    {
        public MyDbContext()  : base("IP=127.0.0.1;PORT=5138;DB=SYSTEM;USER=SYSDBA;PWD=SYSDBA;")
        {
            Database.Connection.Open();
        }

        
        public DbSet<User> Users { get; set; }
    }

    internal class Program
    {
        static void Main(string[] args)
        {
            //DbProviderFactories.SetConfiguration("MySql.Data.MySqlClient", XGProviderFactory.Instance);

            /* DbProviderFactories.SetProviderServices(MicrosoftSqlProviderServices.ProviderInvariantName, MicrosoftSqlProviderServices.Instance);
             // Optional
             DbProviderFactories.SetExecutionStrategy(MicrosoftSqlProviderServices.ProviderInvariantName, () => new MicrosoftSqlAzureExecutionStrategy());
 */

            // 单条插入
            using (var context = new MyDbContext())
            {
                // 1. 添加用户
                var newUser = new User { NAME = "Alice", AGE = 30 };
                context.Entry(newUser).State = EntityState.Added;
                context.SaveChanges();
                Console.WriteLine("User added.");
            }

            //2、单条数据查找和修改
            using (var context = new MyDbContext())
            {
                var newUser = new User {ID = 2, NAME = "Alice", AGE = 30 };
                context.Set<User>().Attach(newUser);
                PropertyInfo[] props = newUser.GetType().GetProperties();
                foreach (PropertyInfo prop in props)
                {
                    if (prop.GetValue(newUser, null) != null)
                    {
                        if (prop.GetValue(newUser, null).ToString() == "Alice")
                            context.Entry(newUser).Property(prop.Name).CurrentValue = null;
                        context.Entry(newUser).Property(prop.Name).IsModified = true;
                    }
                }
                context.SaveChanges();
            }


            // 3、查找单条数据并删除
            using (var dbcontext = new MyDbContext())
            {
                var newUser = new User { ID = 5, NAME = "Alice", AGE = 30 };
                dbcontext.Set<User>().Attach(newUser);
                dbcontext.Entry<User>(newUser).State = EntityState.Deleted;
                dbcontext.SaveChanges();
            }

            //4、查找多条数据并删除
            using (var dbcontext = new MyDbContext())
            {
                var entitys = dbcontext.Set<User>().Where(e => e.ID > 10).ToList();
                entitys.ForEach(m => dbcontext.Entry<User>(m).State = EntityState.Deleted);
                dbcontext.SaveChanges();
            }

            //5、按主键查找单条数据
            using (var dbcontext = new MyDbContext())
            {
                var entitys = dbcontext.Set<User>().Find(2);
                Console.WriteLine("User Select by Primary key");
            }

            //6、按lamuda表达式查询满足条件的第一条记录
            using (var dbcontext = new MyDbContext())
            {
                var user = dbcontext.Set<User>().FirstOrDefault(u => u.NAME == "Alice");
                if (user != null)
                {
                    Console.WriteLine($"User found: {user.NAME}, Age: {user.AGE}");
                }
            }

            //7、查询表中全部数据
            using (var dbcontext = new MyDbContext())
            {
                var data = dbcontext.Set<User>().ToList();
                Console.WriteLine("Slect All");
            }

            //8、查询表中所有满足lamuda表达式的记录
            using (var dbcontext = new MyDbContext())
            {
                var data =  dbcontext.Set<User>().Where(e => e.ID > 10).ToList();
                Console.WriteLine("Slect ID > 10");
            }

            //9、按sql语句查询满足条件的记录
            using (var dbcontext = new MyDbContext())
            {
                var data =  dbcontext.Database.SqlQuery<User>("select * from Users;").ToList<User>();
                Console.WriteLine("sql select");
            }

            //10、分页查询满足条件的记录
            using (var dbcontext = new MyDbContext())
            {
                var data = dbcontext.Set<User>().OrderBy(e => e.ID).Skip(2).Take(2).ToList();
                Console.WriteLine("select limit");
            }

        }
    }
}

